package com.cn.demo4.dao.impl;

import com.cn.demo4.dao.UserDao;
import com.cn.demo4.model.PageModel;
import com.cn.demo4.model.UserModel;
import com.cn.demo4.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public int add(UserModel userModel) {
        String sql = "insert into user(username,password,phone,email,age,sex,birthday,hobby,create_time,update_time)" +
                "values('" + userModel.getUserName() + "'," +
                "'" + userModel.getPassword() + "'," +
                "'" + userModel.getPhone() + "'," +
                "'" + userModel.getEmail() + "'," +
                "'" + userModel.getAge() + "'," +
                "'" + userModel.getSex() + "'," +
                "'" + userModel.getBirthday() + "'," +
                "'" + userModel.getHobby() + "'," +
                "now(),now())";
        return JDBC.upDate(sql);
    }

    @Override
    public boolean findByUserName(String userName) {
        //查询用户名
        String sql = "select * from user where username='" + userName + "'";
        //ResultSet为一个结果集，是个集合
        ResultSet resultSet = JDBC.select(sql);
        boolean isHave = false;
        try {
            //如果数据库存在数据isHave为true，否则为false
            while (resultSet.next()) {
                isHave = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isHave;
    }

    @Override
    public UserModel findByPhoneAndPassword(UserModel userModel) {
        //查询手机号，密码
        String sql = "select * from user where phone='" + userModel.getPhone() + "' and password='" + userModel.getPassword() + "'";
        //ResultSet为一个结果集，是个集合
        ResultSet resultSet = JDBC.select(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return model;
    }


    @Override
    /**
     * 1.查询用户所有数据
     * 2.模糊查询
     */
    public List<UserModel> findAll(UserModel userModel) {
        //1.sql查询语句
        StringBuilder sql = new StringBuilder();
        sql.append("select * from user where true");
        if (!"".equals(userModel.getUserName())) {
            sql.append(" and username like '%").append(userModel.getUserName()).append("%'");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%'");
        }

        if (!"".equals(userModel.getEmail())) {
            sql.append(" and email like '%").append(userModel.getEmail()).append("%'");
        }

        if (!"全部".equals(userModel.getSex()) && userModel.getSex() != null) {
            sql.append(" and sex= '").append(userModel.getSex()).append("'");
        }
        if (!"".equals(userModel.getHobby())) {
            String hobby = userModel.getHobby();
            String[] hobbys = hobby.split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql.append(" and find_in_set('").append(hobbys[i]).append("',hobby) ");
            }
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday >= '").append(userModel.getStartBirthday()).append("'");
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday <= '").append(userModel.getEndBirthday()).append("'");
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday between '").append(userModel.getStartBirthday()).append("' and '").append(userModel.getEndBirthday()).append("'");
        }

        if (userModel.getStatus() != -1) {
            if (userModel.getStatus() == 1) {
                sql.append(" and status=1");
            } else {
                sql.append("  and status=0 or status is null");
            }

        }
        sql.append(" order by id desc");
        //limit ((curPage-1)*pageSize,pageSize )
        sql.append(" limit ").append((userModel.getPageModel().getCurPage() - 1) * userModel.getPageModel().getPageSize()).append(",").append(userModel.getPageModel().getPageSize());
        //2.查询返回一个结果集，只要是查询都用ResultSet
        ResultSet resultSet = JDBC.select(sql.toString());
        //3.因为不知道数据又多少行，所以先定义一个空集合来存放
        return getData(resultSet);
    }

    /**
     * 拿到数据总条数
     *
     * @param userModel
     * @return
     */
    @Override
    public int getCount(UserModel userModel) {
        //1.sql查询语句
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) count from user where true");
        if (!"".equals(userModel.getUserName())) {
            sql.append(" and username like '%").append(userModel.getUserName()).append("%'");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%'");
        }

        if (!"".equals(userModel.getEmail())) {
            sql.append(" and email like '%").append(userModel.getEmail()).append("%'");
        }

        if (!"全部".equals(userModel.getSex()) && userModel.getSex() != null) {
            sql.append(" and sex= '").append(userModel.getSex()).append("'");
        }
        if (!"".equals(userModel.getHobby())) {
            String hobby = userModel.getHobby();
            String[] hobbys = hobby.split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql.append(" and find_in_set('").append(hobbys[i]).append("',hobby) ");
            }
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday >= '").append(userModel.getStartBirthday()).append("'");
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday <= '").append(userModel.getEndBirthday()).append("'");
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday between '").append(userModel.getStartBirthday()).append("' and '").append(userModel.getEndBirthday()).append("'");
        }
        if (userModel.getStatus() != -1) {
            if (userModel.getStatus() == 1) {
                sql.append(" and status=1");
            } else {
                sql.append(" and status is null and status=0");
            }

        }
        sql.append(" order by id desc");
        ResultSet resultSet = JDBC.select(sql.toString());
        int count = 0;
        try {
            while (resultSet.next()) {

                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 根据手机号查询用户名
     * @param phone
     * @return
     */
    @Override
    public UserModel findByPhone(String phone) {
        String sql = "select username from user where phone='" + phone + "'";
        ResultSet resultSet = JDBC.select(sql);
        List<UserModel> list = getUserName(resultSet);
        return list.size() > 0 ? list.get(0) : null;
    }


    /**
     * 得到用户名
     * @param resultSet
     * @return
     */
    private List<UserModel> getUserName(ResultSet resultSet) {
        List<UserModel> list = new ArrayList<>();
        //4.循环遍历数据，因为ResultSet有异常所以先写个try/cath语句
        try {
            while (resultSet.next()) {
                //5.因为最总要返回一个list，所以要调用UserModel
                UserModel model = new UserModel();//当前model为空值，所以要先赋值
                model.setUserName(resultSet.getString("username"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int del(Integer id) {
        //1.sql删除语句
        String sql = "delete from user where id =" + id;
        return JDBC.upDate(sql);
    }

    /**
     * 查询所有数据
     *
     * @param id
     * @return
     */
    @Override
    public UserModel findById(Integer id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.select(sql);
        List<UserModel> list = getData(resultSet);
        return list.size() > 0 ? list.get(0) : null;
    }

    /**
     * 前台编辑操作
     *
     * @param userModel
     * @return
     */
    @Override
    public int edit(UserModel userModel) {
        String sql = "update user set " ;
                if(userModel.getUserName() != null && !userModel.getUserName().equals("")){
                   sql += "username='" + userModel.getUserName() + "'," ;
                }
                if(userModel.getPhone() != null && !userModel.getPhone().equals("")){
                    sql+="phone='" + userModel.getPhone() + "'," ;
                }
                if(userModel.getEmail() != null && !userModel.getEmail().equals("")){
                    sql+="email='" + userModel.getEmail() + "'," ;
                }
                if(userModel.getAge() != null && !userModel.getAge().equals("")){
                    sql+="age='" + userModel.getAge() + "'," ;
                }
                if(userModel.getSex() != null && !userModel.getSex().equals("")){
                    sql+="sex='" + userModel.getSex() + "'," ;
                }
                if(userModel.getBirthday() != null && !userModel.getBirthday().equals("")){
                    sql+="birthday='" + userModel.getBirthday() + "',";
                }
                if(userModel.getHobby() != null && !userModel.getHobby().equals("")){
                    sql+= "hobby='" + userModel.getHobby() + "'," ;
                }
                sql+="update_time=now()" +
                " where id=" + userModel.getId();
        return JDBC.upDate(sql);
    }

    @Override
    public int editPassword(UserModel userModel) {
        String sql = "update user set" +
                " password='" + userModel.getPassword() + "'," +
                "update_time=now()" +
                " where id =" + userModel.getId();
        return JDBC.upDate(sql);
    }


//    public List<UserModel> findAll() {
//        //1.sql查询语句
//        String sql = "select * from user order by id desc";
//        //2.查询返回一个结果集，只要是查询都用ResultSet
//        ResultSet resultSet = JDBC.select(sql);
//        //3.因为不知道数据又多少行，所以先给给你个一个空集合来存放
//
//
//        return getData(resultSet);
//    }


    /**
     * 公共方法查询数据
     *
     * @param resultSet
     * @return
     */
    private List<UserModel> getData(ResultSet resultSet) {
        List<UserModel> list = new ArrayList<>();
        //4.循环遍历数据，因为ResultSet有异常所以先写个try/cath语句
        try {
            while (resultSet.next()) {
                //5.因为最总要返回一个list，所以要调用UserModel
                UserModel model = new UserModel();//当前model为空值，所以要先赋值
                model.setId(resultSet.getInt("id"));//括号中的属性值对应数据库中的列名
                model.setUserName(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setEmail(resultSet.getString("email"));
                model.setSex(resultSet.getString("sex"));
                model.setAge(resultSet.getInt("age"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setStatus(resultSet.getInt("status"));
                model.setCreateTime(resultSet.getString("create_time"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
